/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.edu.ifal.softhouse.bd;

import br.edu.ifal.softhouse.modelo.Cliente;
import br.edu.ifal.softhouse.modelo.FabricaConexao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Carmelita SBO
 */
public class ClienteBD {

    public void insereCliente(Cliente c) throws SQLException {
        FabricaConexao fc = new FabricaConexao();
        Connection con = fc.getConnection();
        String sql = "insert into contatos"
                + "(NomeCliente,sexo,cep,endereco,num,complemento,bairro,estado,cidade,TelefoneRes,Cel,email,estudante,periodo,serie,escola,nomePai,nomeMae,cpf, rg )"
                + " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setString(1, c.getNomeCliente());
        //stmt.setString(2, c.getDataNascimento());
        stmt.setString(2, c.getSexo());
        stmt.setString(3, c.getCpf());
        stmt.setString(4, c.getRg());
        stmt.setString(5, c.getCep());
        stmt.setString(6, c.getEndereco());
        stmt.setInt(7, c.getNum());
        stmt.setString(8, c.getComplemento());
        stmt.setString(9, c.getBairro());
        stmt.setString(10, c.getEstado());
        stmt.setString(11, c.getCidade());
        stmt.setString(12, c.getTelefoneRes());
        stmt.setString(13, c.getCel());
        //stmt.setString(13, c.getTipoCliente());
        stmt.setString(14, c.getEmail());
        stmt.setString(15, c.getEstudante());
        stmt.setString(16, c.getPeriodo());
        stmt.setString(17, c.getSerie());
        stmt.setString(18, c.getEscola());
        stmt.setString(19, c.getNomePai());
        stmt.setString(20, c.getNomeMae());

        stmt.execute();
        stmt.close();
        con.close();
    }

    public void alteraCliente(Cliente cliente) throws SQLException {
        FabricaConexao fc = new FabricaConexao();
        Connection con = fc.getConnection();

        String sql = "update clientes set NomeCliente=?, dataNascimento=?, sexo=?, rg=?, cep=?, endereco=?, num=?, complemento=?, bairro=?, estado=?, cidade=?, TelefoneRes=?, Cel=?,tipoCliente=?, email=, estudante=?, periodo=?, serie=?, escola=?,nomePai=?,nomeMae=? where cpf=?";
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setString(1, cliente.getNomeCliente());
        stmt.setString(2, cliente.getSexo());
        stmt.setString(3, cliente.getRg());
        stmt.setString(4, cliente.getCep());
        stmt.setString(5, cliente.getEndereco());
        stmt.setInt(6, cliente.getNum());
        stmt.setString(7, cliente.getComplemento());
        stmt.setString(8, cliente.getBairro());
        stmt.setString(9, cliente.getEstado());
        stmt.setString(10, cliente.getCidade());
        stmt.setString(11, cliente.getTelefoneRes());
        stmt.setString(12, cliente.getCel());
        stmt.setString(13, cliente.getEmail());
        stmt.setString(14, cliente.getEstudante());
        stmt.setString(15, cliente.getPeriodo());
        stmt.setString(16, cliente.getSerie());
        stmt.setString(17, cliente.getEscola());
        stmt.setString(18, cliente.getNomePai());
        stmt.setString(19, cliente.getNomeMae());
        stmt.setString(20, cliente.getCpf());

        stmt.execute();
        stmt.close();
    }

    public Cliente buscaClientePeloCpf(String cpf) throws SQLException {
        FabricaConexao fc = new FabricaConexao();
        Connection con = fc.getConnection();
        String sql = "select *  from cliente where cpf=?";
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setString(1, cpf);
        ResultSet rs = stmt.executeQuery();
        
        Cliente c1 = new Cliente();
        while (rs.next()) {
            c1.setNomeCliente(rs.getString("NomeCliente"));
            c1.setSexo(rs.getString("Sexo"));
            c1.setCpf(rs.getString("Cpf"));
            c1.setRg(rs.getString("Rg"));
            c1.setCep(rs.getString("Cep"));
            c1.setEndereco(rs.getString("Endereco"));
            c1.setNum(rs.getInt("Num"));
            c1.setComplemento(rs.getString("Complemento"));
            c1.setBairro(rs.getString("Bairro"));
            c1.setEstado(rs.getString("Estado"));
            c1.setCidade(rs.getString("Cidade"));
            c1.setTelefoneRes(rs.getString("TelefoneRes"));
            c1.setCel(rs.getString("Cel"));
            //c1.set(rs.getString(""));
        }
        rs.close();
        stmt.close();
        con.close();
        return c1;
    }
    
    public void removeCliente(Cliente c) throws SQLException {
        FabricaConexao fc = new FabricaConexao();
        Connection con = fc.getConnection();
        
        PreparedStatement stmt = con.prepareStatement("delete"
                + " from cliente where cpf=?");
        stmt.setString(1, c.getCpf());
        stmt.execute();
        stmt.close();
        con.close();
     }
}

